IF EXISTS (SELECT 1 FROM sysobjects where name='UserDetailsView' and type='v')
DROP VIEW UserDetailsView
GO

--SELECT * FROM UserDetailsView
CREATE VIEW UserDetailsView
AS
SELECT um.PKey,
	COALESCE(CONVERT(varchar,aprv.PKey),'') AS ApprovalMainPKey,
	um.UserName,
	um.UserPassword,
	um.SecQues,
	um.SecAns,
	COALESCE(aprv.ApprovalState,'--') AS ApprovalState,
	COALESCE(CONVERT(varchar,aprv.ApprovalDate,105)+' '+CONVERT(varchar,aprv.ApprovalDate,108),'--') AS ApprovalDate,
	COALESCE(aprv.Comments,'') AS Comments,
	um.LoginCount,
	um.NeedsPswdChng,
	um.DateRegistered
	
FROM UserMain um LEFT OUTER JOIN
(
	SELECT PKey,
		UserMainPKey,
		ApprovalState,
		Comments,
		ApprovalDate
	FROM ApprovalMain am1
	WHERE am1.Status <> 'd'
		and am1.PKey IN 
		(
			SELECT TOP 1 PKey
			FROM ApprovalMain am2
			WHERE am2.Status <> 'd'
				and am1.UserMainPKey = am2.UserMainPKey
			ORDER BY ApprovalDate DESC
		)
) aprv ON um.PKey=aprv.UserMainPKey

WHERE um.Status<>'d'
